Full Table Scans

Full table scans have an image problem. Many Oracle developers - usually those working on OLTP systems - are told early in their careers that Full Table Scans are bad. Period! Many will then hold on to this prejudice and never learn the truth.

Full Table Scans can be good when:

• Used on a small table (< 500 rows)
• Used in a query returning more than a few percent of the rows in a table (see High Volume SQLs for a brief discussion on this point)
• Used in a Sort-Merge or Hash join, but only when these join methods are intended.

Full Table Scans are bad when:

• Used on a large table where indexed or hash cluster access is preferred.
• Used on a medium-large table (> 500 rows) as the outer table of a Nested Loop join.
• Used on a medium-large table (> 500 rows) in a Nested Sub-Query.
• Used in a Sort-Merge or Hash join when a Nested Loop join is preferred.

To check for a full table scan, first run your SQL through Explain Plan and check for a line like:

TABLE ACCESS (FULL) MY_TABLE_NAME

To find out why Oracle is not using index/cluster access, see Low Volume SQLs. Remember, read the situations above when a Full Table Scan can be good; if this is one of those cases, hit the back button and look for a different problem.